﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using KZ.Express.H.ENT;
using KZ.Utilities;

namespace KZ.Express.H.DAL
{
    public class BaseDAL
    {
        protected static DataAccessHelper _helper;

        public BaseDAL()
        {
            if (_helper == null)
                _helper = new DataAccessHelper();
        }

        #region Transaction

        public void BeginTransaction()
        {
            _helper.BeginTransaction();
        }

        public void CommitTransaction()
        {
            _helper.CommitTransaction();
        }

        public void RollbackTransaction()
        {
            _helper.RollbackTransaction();
        }

        #endregion Transaction

        private T ConvertFromDataRow<T>(DataRow row)
        {
            Type type = typeof(T);
            T entity = (T)Activator.CreateInstance(type);

            int number = row.Table.Columns.Count;
            for (int i = 0; i < number; i++)
            {
                if (row[i] != DBNull.Value)
                {
                    PropertyInfo pi = type.GetProperty(row.Table.Columns[i].ColumnName);
                    if (pi != null) { pi.SetValue(entity, row[i], null); }
                }
            }

            return entity;
        }

        public object Insert(BaseENT entity)
        {
            try
            {
                List<SqlParameter> parameters = new List<SqlParameter>();
                SqlParameter parameter;
                PropertyInfo[] properties = entity.GetType().GetProperties();
                foreach (PropertyInfo property in properties)
                {
                    object[] obj = property.GetCustomAttributes(typeof(NotInsertUpdateAttribute), true);
                    if (obj.Length == 0)
                    {
                        parameter = new SqlParameter(property.Name, property.GetValue(entity, null));

                        obj = property.GetCustomAttributes(typeof(OutputAttribute), true);
                        if (obj.Length > 0)
                        {
                            parameter.Size = 100;
                            parameter.Direction = ParameterDirection.InputOutput;
                        }

                        parameters.Add(parameter);
                    }
                }

                _helper.ExecuteNonQuery(entity.InsertProcedure, parameters.ToArray());
                parameter = parameters.FirstOrDefault(p => p.Direction == ParameterDirection.InputOutput);
                if (parameter != null)
                    return parameter.Value;
                else
                    return null;
            }
            catch (Exception ex)
            {
                LogDAL.LogEx(entity.InsertProcedure, ex);
                throw new DataAccessException("Lỗi khi thêm mới dữ liệu");
            }
        }

        public object Update(BaseENT entity)
        {
            try
            {
                List<SqlParameter> parameters = new List<SqlParameter>();
                SqlParameter parameter;
                PropertyInfo[] properties = entity.GetType().GetProperties();

                foreach (PropertyInfo property in properties)
                {
                    if (property.Name != "IsRecovery")
                    {
                        object[] obj = property.GetCustomAttributes(typeof(NotInsertUpdateAttribute), true);
                        if (obj.Length == 0)
                        {
                            parameter = new SqlParameter(property.Name, property.GetValue(entity, null));

                            obj = property.GetCustomAttributes(typeof(OutputAttribute), true);
                            if (obj.Length > 0)
                            {
                                if (parameter.Value == null)
                                    parameter.Size = 100;
                                parameter.Direction = ParameterDirection.InputOutput;
                            }

                            parameters.Add(parameter);
                        }
                    }
                }

                _helper.ExecuteNonQuery(entity.UpdateProcedure, parameters.ToArray());
                parameter = parameters.FirstOrDefault(p => p.Direction == ParameterDirection.InputOutput);
                if (parameter != null)
                    return parameter.Value;
                else
                    return null;
            }
            catch (Exception ex)
            {
                LogDAL.LogEx(entity.UpdateProcedure, ex);
                throw new DataAccessException("Lỗi khi cập nhật dữ liệu");
            }
        }

        public void Delete<T>(int id, int userID, bool isLog)
        {
            BaseENT entity = Activator.CreateInstance(typeof(T)) as BaseENT;
            try
            {
                SqlParameter[] parameters = _helper.CreateParameters(6);
                parameters[0].ParameterName = "TableName";
                parameters[0].Value = entity.Table;
                parameters[1].ParameterName = "PrimaryKey";
                parameters[1].Value = entity.PrimaryKey;
                parameters[2].ParameterName = "PrimaryDescription";
                parameters[2].Value = entity.PrimaryDescription;
                parameters[3].ParameterName = "PrimaryValue";
                parameters[3].Value = id;
                parameters[4].ParameterName = "UserID";
                parameters[4].Value = userID;
                parameters[5].ParameterName = "IsLog";
                parameters[5].Value = isLog;

                _helper.ExecuteNonQuery(entity.DeleteProcedure, parameters);
            }
            catch (Exception ex)
            {
                LogDAL.LogEx(entity.DeleteProcedure, ex);
                throw new DataAccessException("Lỗi khi xóa dữ liệu");
            }
        }

        public Dictionary<string, object> ExecuteNonQuery(string query, SearchCriteria search, params string[] outParameters)
        {
            try
            {
                List<SqlParameter> parameters = new List<SqlParameter>();
                if (search != null)
                {
                    foreach (KeyValuePair<string, object> item in search)
                    {
                        SqlParameter parameter = new SqlParameter();
                        parameter.ParameterName = item.Key;
                        parameter.Value = item.Value;

                        parameters.Add(parameter);
                    }
                }

                if (outParameters != null && outParameters.Length > 0)
                {
                    Dictionary<string, object> outValue = new Dictionary<string, object>();
                    foreach (string outParam in outParameters)
                    {
                        int index;
                        if ((index = parameters.FindIndex(p => p.ParameterName == outParam)) != -1)
                        {
                            parameters[index].ParameterName = outParam;
                            parameters[index].Size = Utility.StringParse(parameters[index].Value).Length;
                            parameters[index].Direction = ParameterDirection.InputOutput;
                        }
                        else
                        {
                            SqlParameter parameter = new SqlParameter();
                            parameter.ParameterName = outParam;
                            parameter.Size = int.MaxValue;
                            parameter.Direction = ParameterDirection.Output;

                            parameters.Add(parameter);
                            index = parameters.Count - 1;
                        }
                        outValue.Add(outParam, index);
                    }

                    _helper.ExecuteNonQuery(query, parameters.ToArray());
                    foreach (string outParam in outParameters)
                        outValue[outParam] = parameters[Utility.IntParse(outValue[outParam])].Value;

                    return outValue;
                }
                else
                {
                    _helper.ExecuteNonQuery(query, parameters.ToArray());
                    return null;
                }
            }
            catch (Exception ex)
            {
                LogDAL.LogEx(query, ex);
                throw new DataAccessException("Lỗi khi truy xuất dữ liệu");
            }
        }

        public Dictionary<string, object> ExecuteNonQueryOutParamDefineType(string query, SearchCriteria search, params string[] outParameters)
        {
            try
            {
                List<SqlParameter> parameters = new List<SqlParameter>();
                if (search != null)
                {
                    foreach (KeyValuePair<string, object> item in search)
                    {
                        SqlParameter parameter = new SqlParameter();
                        parameter.ParameterName = item.Key;
                        parameter.Value = item.Value;

                        parameters.Add(parameter);
                    }
                }

                if (outParameters != null && outParameters.Length > 0)
                {
                    Dictionary<string, object> outValue = new Dictionary<string, object>();
                    int length = outParameters.Length;
                    for (int i = 0; i < length; i = i + 2)
                    {
                        int index;
                        if ((index = parameters.FindIndex(p => p.ParameterName == outParameters[i])) != -1)
                        {
                            parameters[index].ParameterName = outParameters[i];
                            parameters[index].DbType = (DbType)Enum.Parse(typeof(DbType), outParameters[i + 1], false);
                            parameters[index].Size = Utility.StringParse(parameters[index].Value).Length;
                            parameters[index].Direction = ParameterDirection.InputOutput;
                        }
                        else
                        {
                            SqlParameter parameter = new SqlParameter();
                            parameter.ParameterName = outParameters[i];
                            parameter.DbType = (DbType)Enum.Parse(typeof(DbType), outParameters[i + 1], false);
                            parameter.Size = int.MaxValue;
                            parameter.Direction = ParameterDirection.Output;

                            parameters.Add(parameter);
                            index = parameters.Count - 1;
                        }
                        outValue.Add(outParameters[i], index);
                    }

                    _helper.ExecuteNonQuery(query, parameters.ToArray());
                    for (int i = 0; i < length; i = i + 2)
                        outValue[outParameters[i]] = parameters[Utility.IntParse(outValue[outParameters[i]])].Value;

                    return outValue;
                }
                else
                {
                    _helper.ExecuteNonQuery(query, parameters.ToArray());
                    return null;
                }
            }
            catch (Exception ex)
            {
                LogDAL.LogEx(query, ex);
                throw new DataAccessException("Lỗi khi truy xuất dữ liệu");
            }
        }

        public T GetByID<T>(object entityID)
        {
            BaseENT entity = Activator.CreateInstance(typeof(T)) as BaseENT;
            try
            {
                SqlParameter[] parameters = _helper.CreateParameters(1);
                parameters[0].ParameterName = entity.PrimaryKey;
                parameters[0].Value = entityID;

                DataTable dt = _helper.ExecuteDataTable(entity.SelectProcedure, parameters);
                if (dt.Rows.Count > 0)
                    return ConvertFromDataRow<T>(dt.Rows[0]);
                return default(T);
            }
            catch (Exception ex)
            {
                LogDAL.LogEx(entity.SelectProcedure, ex);
                throw new DataAccessException("Lỗi khi truy xuất dữ liệu");
            }
        }

        public T GetEntity<T>(SearchCriteria search)
        {
            BaseENT entity = Activator.CreateInstance(typeof(T)) as BaseENT;
            try
            {
                SqlParameter[] parameters = null;
                if (search != null)
                {
                    parameters = _helper.CreateParameters(search.Count);
                    int index = 0;
                    foreach (KeyValuePair<string, object> item in search)
                    {
                        parameters[index].ParameterName = item.Key;
                        parameters[index].Value = item.Value;
                        index++;
                    }
                }

                DataTable dt = _helper.ExecuteDataTable(entity.SelectProcedure, parameters);
                if (dt.Rows.Count > 0)
                    return ConvertFromDataRow<T>(dt.Rows[0]);
                return default(T);
            }
            catch (Exception ex)
            {
                LogDAL.LogEx(entity.SelectProcedure, ex);
                throw new DataAccessException("Lỗi khi truy xuất dữ liệu");
            }
        }

        public T GetEntity<T>(string query, SearchCriteria search)
        {
            BaseENT entity = Activator.CreateInstance(typeof(T)) as BaseENT;
            try
            {
                SqlParameter[] parameters = null;
                if (search != null)
                {
                    parameters = _helper.CreateParameters(search.Count);
                    int index = 0;
                    foreach (KeyValuePair<string, object> item in search)
                    {
                        parameters[index].ParameterName = item.Key;
                        parameters[index].Value = item.Value;
                        index++;
                    }
                }

                DataTable dt = _helper.ExecuteDataTable(query, parameters);
                if (dt.Rows.Count > 0)
                    return ConvertFromDataRow<T>(dt.Rows[0]);
                return default(T);
            }
            catch (Exception ex)
            {
                LogDAL.LogEx(query, ex);
                throw new DataAccessException("Lỗi khi truy xuất dữ liệu");
            }
        }

        public DataTable GetDataTable<T>(SearchCriteria search)
        {
            BaseENT entity = Activator.CreateInstance(typeof(T)) as BaseENT;
            try
            {
                SqlParameter[] parameters = null;
                if (search != null)
                {
                    parameters = _helper.CreateParameters(search.Count);
                    int index = 0;
                    foreach (KeyValuePair<string, object> item in search)
                    {
                        parameters[index].ParameterName = item.Key;
                        parameters[index].Value = item.Value;
                        index++;
                    }
                }

                return _helper.ExecuteDataTable(entity.SelectProcedure, parameters);
            }
            catch (Exception ex)
            {
                LogDAL.LogEx(entity.SelectProcedure, ex);
                throw new DataAccessException("Lỗi khi truy xuất dữ liệu");
            }
        }

        public DataTable GetDataTable(string query, SearchCriteria search)
        {
            try
            {
                SqlParameter[] parameters = null;
                if (search != null)
                {
                    parameters = _helper.CreateParameters(search.Count);
                    int index = 0;
                    foreach (KeyValuePair<string, object> item in search)
                    {
                        parameters[index].ParameterName = item.Key;
                        parameters[index].Value = item.Value;
                        index++;
                    }
                }

                return _helper.ExecuteDataTable(query, parameters);
            }
            catch (Exception ex)
            {
                LogDAL.LogEx(query, ex);
                throw new DataAccessException("Lỗi khi truy xuất dữ liệu");
            }
        }
    }
}
